package com.scalar.chiptrack.operations.process.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;

import com.scalar.ScalarException;
import com.scalar.chiptrack.db.DBTransaction;
import com.scalar.chiptrack.db.DataSource;
import com.scalar.chiptrack.operations.process.Transport;
import com.scalar.chiptrack.utils.SqlQueryMappings;

public class TransportDAO{

    private TransportDAO(){
    }

    public static void saveTransportInfo (DBTransaction dbTransaction, int processStepID, Iterator transportIterator) throws ScalarException{

        Connection conn = null;
        PreparedStatement pstmt = null;
        boolean status = false;
        ResultSet rs = null;
        boolean update = false;
        
        try{
            // Get the connection from regular datasource if its not in a manual transaction
            if (dbTransaction == null) {
                conn = DataSource.getConnection();
            }
            else {
                conn = dbTransaction.getConnection();
            }
            
            // STEP 1
            // SEE IF TRANSPORT LOCATION EXISTS FOR THIS PROCESS STEP ID
            pstmt = conn.prepareStatement(SqlQueryMappings.get("operations.process.transport.doesProcessStepIdExist"));
            pstmt.setInt (1, processStepID);
            rs = pstmt.executeQuery();
            
            if (rs.next()) {
                update = (rs.getInt ("Row_Count") > 0);
            }
            
            rs.close();
            rs = null;
            
            pstmt.close();
            pstmt = null;
            
            
            // STEP 2
            // INSERT OR UPDATE TRANSPORT LOCATION INFORMATION
            if (update) {
                pstmt = conn.prepareStatement(SqlQueryMappings.get("operations.process.transport.deleteTransport"));
				pstmt.setInt(1,processStepID);
				pstmt.executeUpdate();
				pstmt.close();
				pstmt=null;
            }
            pstmt = conn.prepareStatement(SqlQueryMappings.get("operations.process.transport.insertTransport"));
            
            Transport tp = null;

            while (transportIterator.hasNext()) {
                tp = (Transport)transportIterator.next();

                /*if (update) {
                    pstmt.setInt (1, tp.getNormalTime());
                    pstmt.setFloat (2, tp.getNormalCost());
                    pstmt.setInt (3, tp.getExpressTime());
                    pstmt.setFloat (4, tp.getExpressCost());
                    pstmt.setInt (5, processStepID);
                    pstmt.setInt (6, tp.getSourceLocationID());
                    pstmt.setInt (7, tp.getDestinationLocationID());
                }
                else { */
                    pstmt.setInt (1, processStepID);
                    pstmt.setInt (2, tp.getSourceLocationID());
                    pstmt.setInt (3, tp.getDestinationLocationID());
                    pstmt.setInt (4, tp.getNormalTime());
                    pstmt.setFloat (5, tp.getNormalCost());
                    pstmt.setInt (6, tp.getExpressTime());
                    pstmt.setFloat (7, tp.getExpressCost());
               // }
                
                pstmt.addBatch();
            }

            pstmt.executeBatch();
            status = true;
        }
        catch(Exception sql){
            sql.printStackTrace();
            throw new ScalarException ("Could not save the Traport Information to database", sql);
        }
        finally{
            // Commit/Rollback here it selef -- if its not in a manual transaction
            if (dbTransaction == null) {
                try {
                    if (conn != null) {
                        if (status == true) {
                            conn.commit();
                        }
                        else {
                            conn.rollback();
                        }
                    }
                }
                catch (SQLException ignore) {}
            }
            
            if (rs != null) {
                try { rs.close(); } catch (SQLException se) {}
            }
            
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException se) {}
            }
            
            if (conn != null) {
                try { conn.close(); }catch (SQLException se) {}
            }
        }

    }

    
    public static HashMap getTransportInfoMap (int processStepID) throws ScalarException{

        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        HashMap transportMap = null;

        try{
            conn = DataSource.getConnection();
            pstmt = conn.prepareStatement (SqlQueryMappings.get("operations.process.transport.readTransport"));

            pstmt.setInt (1, processStepID);
            rs = pstmt.executeQuery();

            Transport tp = null;
            transportMap = new HashMap();

            int srcLocId = -1;
            int destLocId = -1;
            
            while (rs.next()) {
                tp = new Transport();
                
                srcLocId = rs.getInt ("TT_Source_Location_ID");
                destLocId = rs.getInt ("TT_Destination_Location_ID");
                tp.setSourceLocationID (srcLocId);
                tp.setDestinationLocationID (destLocId);
                
                tp.setNormalTime (rs.getInt("TT_Normal_Time"));
                tp.setNormalCost (rs.getFloat("TT_Normal_Cost"));
                tp.setExpressTime (rs.getInt("TT_Express_Time"));
                tp.setExpressCost (rs.getFloat("TT_Express_Cost"));

                transportMap.put (srcLocId + "|" + destLocId, tp);
            }

        }
        catch(Exception sql) {
            sql.printStackTrace();
            throw new ScalarException ("Reading Transport info failed", sql);
        }
        finally {
            if(pstmt != null) {
                try { pstmt.close(); } catch (SQLException se) {}
            }
            if(conn != null) {
                try{ conn.close(); } catch (SQLException se) {}
            }
        }

        return transportMap;
    }
    
    
    
    public static ArrayList getTransportInfo(int processStepID) throws ScalarException{

        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        ArrayList transportList = null;

        try{
            conn = DataSource.getConnection();
            pstmt = conn.prepareStatement (SqlQueryMappings.get("operations.process.transport.readTransport"));

            pstmt.setInt (1, processStepID);
            rs = pstmt.executeQuery();

            Transport tp = null;
            transportList = new ArrayList();

            while(rs.next()){
                tp = new Transport();
                tp.setSourceLocationID (rs.getInt("TT_Source_Location_ID"));
                tp.setDestinationLocationID (rs.getInt("TT_Destination_Location_ID"));
                tp.setNormalTime (rs.getInt("TT_Normal_Time"));
                tp.setNormalCost (rs.getFloat("TT_Normal_Cost"));
                tp.setExpressTime (rs.getInt("TT_Express_Time"));
                tp.setExpressCost (rs.getFloat("TT_Express_Cost"));

                transportList.add (tp);
            }
        }
        catch(Exception sql) {
            sql.printStackTrace();
            throw new ScalarException ("Reading Transport info failed", sql);
        }
        finally {
            if(pstmt != null) {
                try { pstmt.close(); } catch(SQLException se) {}
            }
            if(conn != null) {
                try{ conn.close(); } catch(SQLException se) {}
            }
        }

        return transportList;
    }

    //Testing purpose only

    public static void main(String[] args){
        /*Transport tp = new Transport();
        Transport tp1 = new Transport();

        tp.setSourceLocationID(3);
        tp.setDestinationLocationID(1);
        tp.setNormalTime(4);
        tp.setNormalCost(20.2);
        tp.setExpressTime(5);
        tp.setExpressCost(150);

        tp1.setSourceLocationID(2);
        tp1.setDestinationLocationID(3);
        tp1.setNormalTime(4);
        tp1.setNormalCost(20.2);
        tp1.setExpressTime(5);
        tp1.setExpressCost(150);


        List list = new ArrayList();
        list.add(tp);
        list.add(tp1);

        try{
                saveTransportInfo(1001, list.iterator());

        }catch(Exception e){

        }*/

        try{
            List list = null;
            list = getTransportInfo(1001);
            Iterator it = list.iterator();

            Transport tp = null;

            while(it.hasNext()){
                    tp = (Transport)it.next();

                    System.out.println("Source Location = "+tp.getSourceLocationID());
                    System.out.println("Destination Location ="+tp.getDestinationLocationID());
                    System.out.println("Normal Cost = "+tp.getNormalCost());
            }
        }
        catch(Exception e){}
    }
}